#include "SQLHelper.h"
#include "SystemHelper.h"

SQLHelper::SQLHelper()
{
    if(QSqlDatabase::contains("qt_sql_default_connection"))
    {
        db = QSqlDatabase::database("qt_sql_default_connection");
    }
    else
    {
        db = QSqlDatabase::addDatabase("QMYSQL");
    }
    table_list = new QVector<QString>;
}

SQLHelper::~SQLHelper()
{
    if(db.open())
    {
        db.close();
        std::cout << "close to mysql OK!" << std::endl;

    }
    delete table_list;
}

int SQLHelper::connect()
{

    db.setHostName("127.0.0.1");      //连接数据库主机名，这里需要注意（若填的为”127.0.0.1“，出现不能连接，则改为localhost)
    db.setPort(3306);                 //连接数据库端口号，与设置一致
    db.setDatabaseName("library_ms");      //连接数据库名，与设置一致
    db.setUserName("root");          //数据库用户名，与设置一致
    db.setPassword(SQL_PASSWORD);   //数据库密码，与设置一致
    if(!db.open())
    {
        std::cout << "connect to mysql failed!" << std::endl;
        return 0;
    }
    std::cout << "connect to mysql OK!" << std::endl;
    return 1;
}

void SQLHelper::creat_table()
{
    if(!db.open())
    {
        std::cout << "connect to mysql failed!" << std::endl;
        return;
    }
    QSqlQuery query = QSqlQuery(db);

    // 用户表：id（主键）、用户名、密码、创建时间、权限
    QString sql1 = "CREATE TABLE `library_ms`.`user` (`id` INT NOT NULL,`name` VARCHAR(45) NOT NULL,`passwd` VARCHAR(45) NOT NULL,`create_time` VARCHAR(45) NOT NULL,`authority` INT NOT NULL,PRIMARY KEY (`id`));";
    if(!query.exec(sql1))
    {
        std::cout << "creat use tabel failed!" << std::endl;
    }
    table_list->push_back("user");

    // 图书表：id（主键）、书名、作者
    QString sql2 = "CREATE TABLE `library_ms`.`books`(`id` INT NOT NULL,`name` VARCHAR(45) NOT NULL,`author` VARCHAR(45) NOT NULL,PRIMARY KEY (`id`))";
    if(!query.exec(sql2))
    {
        std::cout << "creat book tabel failed!" << std::endl;
    }
    table_list->push_back("books");

    //订阅表：id（主键）、用户id、图书id、订阅时间
    QString sql3 = "CREATE TABLE `library_ms`.`subscribe`(`id` INT NOT NULL,`user_id` INT NOT NULL,`book_id` INT NOT NULL,`create_time` VARCHAR(45) NOT NULL,PRIMARY KEY (`id`))";
    if(!query.exec(sql3))
    {
        std::cout << "creat subcribe tabel failed!" << std::endl;
    }
    table_list->push_back("subscribe");
}

void SQLHelper::get_table(QVector<QString> *result)
{
    for (QVector<QString>::const_iterator iter = table_list->begin();iter != table_list->end(); iter++)
    {
        result->push_back(*iter);
    }
}

/*
    Example:
        QVector<QVector<QString>*> result;
        sql_helper.search_table("books",NULL,"NULL", &result);

        for(int i = 0; i < result.size(); i++)
        {
            for(int j = 0; j < result[i]->size(); j++)
            {
                std::cout << (*result[i])[j].toStdString() << " ";
            }
            std::cout << std::endl;
        }
*/

int SQLHelper::search_table(QString table_name, QVector<QString>* attr, QString condition, QVector<QVector<QString>*> *result)
{
    if(!db.open())
    {
        std::cout << "connect to mysql failed!" << std::endl;
        return 0 ;
    }

    QString column = "";
    if(attr == NULL)
    {
        column += "*";
    }
    else
    {
        for(QVector<QString>::const_iterator iter = attr->begin(); iter != attr->end(); iter++)
        {
            column += *iter + ',';
        }
        column = column.left(column.length() - 1);
    }

    QString sql = "SELECT "+ column +" FROM `library_ms`.`" + table_name;
    if(!condition.isNull())
    {
        sql += "` where " + condition + ";";
    }
    else
    {
        sql += "`;";
    }
    std::cout << "do : " << sql.toStdString() << std::endl;

    QSqlQuery query = QSqlQuery(db);
    if(!query.exec(sql))
    {
        std::cout << "search failed!" << std::endl;
        return 0;
    }

    int column_num=query.record().count();  //属性个数
    while (query.next()) {
        QVector<QString> *line = new QVector<QString>;
        for(int i = 0; i < column_num; i++)
        {
            line->push_back(query.value(i).toString());
        }
        result->push_back(line);
    }
    return 1;
}

/*
    Example:
        QVector<QString> v;
        v.push_back("2");
        v.push_back("root");
        v.push_back("root");
        sql_helper.insert_table("books",&v);
*/

int SQLHelper::insert_table(QString table_name,QVector<QString>* values)
{
    if(values == NULL)
    {
        std::cout << "insert values is null!" << std::endl;
        return 0;
    }
    if(!db.open())
    {
        std::cout << "connect to mysql failed!" << std::endl;
        return 0;
    }

    QString val = "";
    for(QVector<QString>::const_iterator iter = values->begin(); iter != values->end(); iter++)
    {
        val += "'" + *iter + "',";
    }
    val = val.left(val.length() - 1);
    QString sql = "INSERT INTO `library_ms`.`" + table_name + "` VALUES (" + val + ");";
    std::cout << "do : " << sql.toStdString() << std::endl;

    QSqlQuery query = QSqlQuery(db);
    if(!query.exec(sql))
    {
        std::cout << "insert failed!" << std::endl;
        return 0;
    }

    return 1;
}

int SQLHelper::delete_table(QString table_name, QString condition)
{
    if(!db.open())
    {
        std::cout << "connect to mysql failed!" << std::endl;
        return 0;
    }

    QString sql = "DELETE FROM " + table_name + " WHERE " + condition + ";";
    std::cout << "do : " << sql.toStdString() << std::endl;

    QSqlQuery query = QSqlQuery(db);
    if(!query.exec(sql))
    {
        std::cout << "delete failed!" << std::endl;
        return 0;
    }

    return 1;
}

int SQLHelper::update_table(QString table_name, QVector<QString>* attr, QVector<QString>* values, QString condition)
{
    if(!db.open())
    {
        std::cout << "connect to mysql failed!" << std::endl;
        return 0;
    }

    QString set = "";
    QVector<QString>::const_iterator iter1 = attr->begin();
    QVector<QString>::const_iterator iter2 = values->begin();
    while (iter1 != attr->end())
    {
        set += *iter1 + "='" + *iter2 + "',";
        iter1++;
        iter2++;
    }
    set = set.left(set.length() - 1);
    QString sql = "UPDATE " + table_name +" SET " + set + " WHERE " + condition + ";";
    std::cout << "do : " << sql.toStdString() << std::endl;

    QSqlQuery query = QSqlQuery(db);
    if(!query.exec(sql))
    {
        std::cout << "delete failed!" << std::endl;
        return 0;
    }

    return 1;
}

QSqlQuery* SQLHelper::exec_sql(QString sql)
{
    if(!db.open())
    {
        std::cout << "connect to mysql failed!" << std::endl;
        return NULL;
    }

    std::cout << "do : " << sql.toStdString() << std::endl;
    QSqlQuery *query = new QSqlQuery(db);
    if(!query->exec(sql))
    {
        std::cout << "exec failed!" << std::endl;
        delete query;
        return NULL;
    }
    return query;
}

QString SQLHelper::get_attr(QString table_name)
{
    QString user_attr = "(`id`, `name`, `passwd`, `create_time`, `authority`)";
    QString books_attr = "(`id`, `name`, `author`, `state`)";
    QString subscribe_attr = "(`id`, `user_id`, `book_id`, `create_time`)";

    if(table_name == "user")
    {
        return user_attr;
    }
    else if(table_name == "books")
    {
        return books_attr;
    }
    else if(table_name == "subcribe")
    {
        return subscribe_attr;
    }
    return "";
}
